SYSDATE
Returns current system date and time.
NOW
Returns current system date and time.
CURRENT_TIMESTAMP
Returns current system date and time.
CURRENT_DATE
Returns current system date.
CURRENT_TIME
Returns current system time.
TOSTRING
Converts date to string using specified format.
TODATE
Converts string to date using specified format.
SYSDATE, NOW AND CURRENT_TIMESTAMP Functions
Returns current system date and time.
Syntax:
SYSDATE
NOW
CURRENT_TIMESTAMP
Example:
SELECT LastInvoiceDate, NOW as CurDate
FROM Customer
WHERE LastInvoiceDate < NOW
CURRENT_DATE Function
Returns current system date.
Syntax:
CURRENT_DATE
Example:
SELECT LastInvoiceDate, CURRENT_DATE as CurDate
FROM Customer
WHERE LastInvoiceDate < NOW
CURRENT_TIME Function
Returns current system time.
Syntax:
CURRENT_TIME
Example:
SELECT LastInvoiceDate, CURRENT_TIME as CurTime
FROM Customer
WHERE LastInvoiceDate < NOW
TOSTRING Function
Converts date to string using specified format.
Syntax:
TOSTRING( DateValue, DateFormat )
Arguments
DateValue
Is an expression of date type that specifies the source date.
DateFormat
Is an expression of string or wide string type that specifies date format for conversion DateValue to string.
DateFormat strings are composed from specifiers that represent values to be inserted into the formatted string. Some specifiers (such as "d"), simply format numbers or strings. Other specifiers (such as "/") refer to locale-specific strings.
In the following table, specifiers are given in upper case. Case is ignored in formats.
Specifier
Displays
-
Displays date separator '-'.
/
Displays date separator '/'.
.
Displays date separator '.'.
,
Displays date separator ','.
:
Displays date separator ':'.
;
Displays date separator ';'.
'TEXT'
Displays the text that will be included in the result of TOSTRING function without any conversion. The leading and trailing quotes will be omitted.
YYYY
or
YEAR
Displays the year as a four-digit number (0000-9999)
YY
Displays the year as a two-digit number (00-99)
Q
Displays the quarter of the year (1-4). 1 means months January, February and March, 2 means months April, May and June, 3 means months July, August and September, 4 means months October, November and December.
MONTH
Displays the month as a full name (January-December).
MON
Displays the month as an abbreviation (Jan-Dec).
MM
Displays the month as a number with a leading zero (01-12).
M
Displays the month as a number without a leading zero (1-12).
RM
Displays the month in roman numeric format (I - XII).
DDD
Displays the day of the year (1-366) without a leading zero.
DD
Displays the day of the month (01-31) with a leading zero.
D
Displays the day of the month (1-31) without a leading zero.
DAY
Displays the day as an abbreviation (Sun-Sat).
DY
Displays the day as an 2 symbol abbreviation (Su-Sa).
DW
Displays the day of week (1-7)
HH
HH12
Displays the hour with a leading zero (01-12).
HH24
Displays the hour with a leading zero (01-24).
H
H12
Displays the hour without a leading zero (1-12).
H24
Displays the hour without a leading zero (1-24).
NN
Displays the minute with a leading zero (00:59).
N
Displays the minute without a leading zero (0:59).
SS
Displays the second with a leading zero (00:59).
S
Displays the second without a leading zero (00:59).
AMPM
Displays the meridian indicator AM.
Example:
SELECT TOSTRING(LastInvoiceDate,"'Today is' mm/dd/yyyy hh24:nn:ss ' Wow !!!'") Formated_Date, LastInvoiceDate FROM Customer
TODATE Function
Converts string to date using specified format.
Syntax:
TODATE( StringValue, DateFormat )
Arguments
StringValue
Is an expression of string or wide string type that specifies the source string.
DateFormat
Is an expression of string or wide string type that specifiec date format for the StringValue.
DateFormat strings are composed from specifiers that represent values to be inserted into the formatted string. Some specifiers (such as "d"), simply format numbers or strings. Other specifiers (such as "/") refer to locale-specific strings.
In the following table, specifiers are given in upper case. Case is ignored in formats.
Specifier
Specifies
-
Specifies date separator '-'.
/
Specifies date separator '/'.
.
Specifies date separator '.'
,
Specifies date separator ','.
:
Specifies date separator ':'.
;
Specifies date separator ';'.
'text'
Specifies the text that will be ignored by TODATE function. The leading and trailing quotes will be omitted.
YYYY
or
YEAR
Specifies the year as a four-digit number (0000-9999)
YY
Specifies the year as a two-digit number (00-99)
Q
Specifies the quarter of the year (1-4). 1 means months January, February and March, 2 means months April, May and June, 3 means months July, August and September, 4 means months October, November and December.
MM
Specifies the month as a number with a leading zero (01-12).
M
Specifies the month as a number without a leading zero (1-12).
DDD
Specifies the day of the year (1-366) without a leading zero.
DD
Specifies the day of the month (01-31) with a leading zero.
D
Specifies the day of the month (1-31) without a leading zero.
DW
Specifies the day of week (1-7)
HH
HH12
Specifies the hour with a leading zero (01-12).
HH24
Specifies the hour with a leading zero (01-24).
H
H12
Specifies the hour without a leading zero (1-12).
H24
Specifies the hour without a leading zero (1-24).
NN
Specifies the minute with a leading zero (00:59).
N
Specifies the minute without a leading zero (0:59).
SS
Specifies the second with a leading zero (00:59).
S
Specifies the second without a leading zero (00:59)
AMPM
Specifies the meridian indicator AM.
Example:
SELECT LastInvoiceDate, NOW as CurDate
FROM Customer
WHERE LastInvoiceDate < TODATE('12/16/2002 11:10:30 am','MM/DD/YYYY hh:nn:ss ampm')
Aggregate Functions
Operate on a collection of values but return a single, summarizing value.
COUNT
Returns the number of items in a group.
SUM
Returns the sum of the values in the expression.
AVG
Returns the average of the values in a group.
MIN
Returns the minimum value in the expression.
MAX
Returns the maximum value in the expression.
COUNT Function
Returns the number of items in a group.
Syntax:
COUNT ( expression | * )
Arguments
expression
Is an expression of any type except Blob types. Aggregate functions and subqueries are not permitted.
*
Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain null values.
Examples:
SELECT COUNT(*) FROM Orders
SELECT COUNT(OrderNo), ShipVIA
FROM Orders
GROUP BY ShipVIA
SUM Function
Returns the sum of all the values in the expression. SUM can be used with numeric columns only. Null values are ignored.
Syntax
SUM ( expression )
Arguments
expression
Is a constant, column, or function, and any combination of arithmetic operators. expression is an expression of the exact numeric or approximate numeric data type category. Aggregate functions and subqueries are not permitted.
Example:
SELECT SUM(AmountPaid)
FROM Orders
WHERE PaymentMethod='Visa'
AVG Function
Returns the average of the values in a group. Null values are ignored.
Syntax
AVG ( expression )
Arguments
expression
Is an expression of the exact numeric or approximate numeric data type category. Aggregate functions and subqueries are not permitted.
Example:
SELECT AVG(AmountPaid)
FROM Orders
WHERE PaymentMethod='Cash'
MIN Function
Returns the minimum value in the expression.
Syntax
MIN ( expression )
Arguments
expression
Is a constant, column name, or function, and any combination of arithmetic operators. MIN can be used with numeric columns only. Aggregate functions and subqueries are not permitted.
Example:
SELECT MIN(OrderNo)
FROM Orders
MAX Function
Returns the maximum value in the expression.
Syntax
MAX ( expression )
Arguments
expression
Is a constant, column name, or function, and any combination of arithmetic operators. MAX can be used with numeric columns only. Aggregate functions and subqueries are not permitted.
Example:
SELECT MAX(SaleDate)
FROM Orders
CAST Function
The CAST function converts a specified value to the specified data type.
Syntax
CAST( value, data_type )
Arguments
value
Is an expression of any valid data type.
data_type
Is a constant that specifies data type for converting the value specified by Value.
CAST function can be used with the following data types:
Data type
Description
AutoInc
Auto incremental 32-bit unsigned integer.
Currency
Floating point number.
Date
Date value.
DateTime
DateTime value.
Float
Floating point number.
Integer
32-bit signed integer.
LargeInt
64-bit signed integer.
Logical
Boolean value.
SmallInt
16-bit signed integer
String
Fixed length string (may be up to 2^32 symbols)
Time
Time value.
WideString
Fixed length Unicode string (may be up to 2^32 symbols)
Word
16-bit unsigned integer.
Example:
SELECT Customer.*, CAST(CURRENT_DATE,STRING) as CurDate FROM Customer
TOBLOB Function
The TOBLOB function converts a specified string value to the BLOB value.
Syntax
TOBLOB( value [, format] )
Arguments
value
Is a string value that can be converted to a BLOB value using specified format.
format
Two formats are supported:
MIME64 - MIME64 standard format (used in e-mail)
HEX - upper case hexadecimal numbers
Default format is MIME64 (typically provides smaller string length).
Example:
INSERT INTO jpeg VALUES ('ContactImage', TOBLOB ('QWlkQWltIFNvZnR3YXJlDQpIZXJlIHRvIEhlbHANCg==',MIME64), NULL, 1);
LASTAUTOINC Function
The LASTAUTOINC function returns the last autoinc value from a specified table.
Syntax
LASTAUTOINC( table_name, column_name )
Arguments
table_name
Is a string constant that specifies table name for getting the last autoinc value.
column_name
Is a string constant that specifies autoinc field name for getting the last autoinc value.
Example:
INSERT INTO Employee (Name,DeptID) VALUES ('John Smith',LASTAUTOINC( Department, ID ))
Related Topics